package com.vicki.music;

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.provider.MediaStore;
import android.util.Log;

import com.vicki.music.MediaUtils.MediaInfo;

public class MediaSqlite {

	public static final String LOG_DATABASE = "MediaSqlite";
	private static final String DB_NAME = "media_player";
	private MSQLiteOpenHelper sOpenHelper;

	public static String[] MEDIA_ARGS = new String[] {
			MediaStore.Audio.Media.TITLE, MediaStore.Audio.Media.DURATION,
			MediaStore.Audio.Media.ARTIST, MediaStore.Audio.Media.DISPLAY_NAME,
			MediaStore.Audio.Media.DATA, MediaStore.Audio.Media.ALBUM
	};

	public MediaSqlite(Context context) {
		sOpenHelper = new MSQLiteOpenHelper(context, DB_NAME, null, 1);
		SQLiteDatabase database = sOpenHelper.getWritableDatabase();
		database.close();
	}

	public class MSQLiteOpenHelper extends SQLiteOpenHelper {
		public static final String TABLE_NAME = "medias";
		public static final String TITLE = "title";
		public static final String ALBUM = "album";
		public static final String ARTIST = "artist";
		public static final String PATH = "path";
		public static final String DURATION = "duration";
		public static final String DISPLAY_NAME = "display_name";
		public static final String PLAYLIST = "playlist";

		public MSQLiteOpenHelper(Context context, String name,
				CursorFactory factory, int version) {
			super(context, name, factory, version);
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			Log.i(LOG_DATABASE, "onCreate()");
			db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLE_NAME + " ("
					+ TITLE + " VARCHAR," + ALBUM + " VARCHAR," + ARTIST
					+ " VARCHAR," + PATH + " VARCHAR," + DURATION + " VARCHAR,"
					+ DISPLAY_NAME + " VARCHAR," + PLAYLIST + " VARCHAR)");
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

			db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
			onCreate(db);
		}
	}

	public void update(MediaInfo mediaInfo) {
		SQLiteDatabase database = sOpenHelper.getWritableDatabase();
		ContentValues values = setInfo(mediaInfo);
		database.update(MSQLiteOpenHelper.TABLE_NAME, values,
				MSQLiteOpenHelper.PLAYLIST + " = ?",
				new String[] { mediaInfo._playlist });
		database.close();
	}

	public void insert(MediaInfo mediaInfo) {
		Log.i(LOG_DATABASE, mediaInfo._display_name);
		Cursor cursor = query(mediaInfo._path, 4);
		if (cursor.getCount() != 0) {
			cursor.close();
			Log.i(LOG_DATABASE, "cursor is not empty " + cursor.getCount());
			return;
		}
		Log.i(LOG_DATABASE, "cursor is empty");
		cursor.close();
		SQLiteDatabase database = sOpenHelper.getWritableDatabase();
		ContentValues values = setInfo(mediaInfo);
		database.insert(MSQLiteOpenHelper.TABLE_NAME, null, values);
		database.close();
	}

	public List<MediaInfo> queryAll() {

		SQLiteDatabase database = sOpenHelper.getReadableDatabase();

		Cursor cursor = database.query(true, MSQLiteOpenHelper.TABLE_NAME,
				new String[] { MSQLiteOpenHelper.PLAYLIST }, null, null, null,
				null, null, null);

		List<MediaInfo> medias = new ArrayList<MediaInfo>();
		cursor.moveToFirst();
		for (int i = 0; i < cursor.getCount(); i++) {
			MediaInfo mediaInfo = new MediaInfo();
			mediaInfo.set_playlist(cursor.getString(0));
			medias.add(mediaInfo);
			cursor.moveToNext();
		}
		cursor.close();
		Log.i(LOG_DATABASE, cursor.getCount() + "");
		database.close();
		return medias;
	}

	public List<String> query(String[] projection) {
		SQLiteDatabase database = sOpenHelper.getReadableDatabase();
		ArrayList<String> playlists = new ArrayList<String>();
		Cursor cursor = database.query(MSQLiteOpenHelper.TABLE_NAME,
				projection, null, null, null, null, null);
		if (cursor == null)
			return null;
		cursor.moveToFirst();
		final int count = cursor.getCount();
		for (int i = 0; i < count; i++) {
			String father = cursor.getString(0);
			playlists.add(father);
			cursor.moveToNext();
		}
		cursor.close();
		database.close();
		MediaUtils.unduplicate(playlists);
		return playlists;
	}

	public Cursor query(String value, int cmd) {
		SQLiteDatabase database = sOpenHelper.getReadableDatabase();
		String clause;
		switch (cmd) {
		case 0:
			clause = MSQLiteOpenHelper.PLAYLIST;
			break;
		case 1:
			clause = MSQLiteOpenHelper.TITLE;
			break;
		case 2:
			clause = MSQLiteOpenHelper.ALBUM;
			break;
		case 3:
			clause = MSQLiteOpenHelper.ARTIST;
			break;
		case 4:
			clause = MSQLiteOpenHelper.PATH;
			break;
		default:
			return null;
		}
		Cursor cursor = database.query(MSQLiteOpenHelper.TABLE_NAME, null,
				clause + "= ?", new String[] { value }, null, null, null);
		// database.close();
		return cursor;
	}

	public void deleteByName(String name, int cmd) {
		SQLiteDatabase database = sOpenHelper.getWritableDatabase();
		String clause;
		switch (cmd) {
		case 0:
			clause = MSQLiteOpenHelper.PLAYLIST;
			break;
		case 1:
			clause = MSQLiteOpenHelper.TITLE;
			break;
		case 2:
			clause = MSQLiteOpenHelper.ALBUM;
			break;
		case 3:
			clause = MSQLiteOpenHelper.ARTIST;
		default:
			return;
		}
		database.delete(MSQLiteOpenHelper.TABLE_NAME, clause + " = ?",
				new String[] { name });
		database.close();
	}

	private ContentValues setInfo(MediaInfo mediaInfo) {
		ContentValues values = new ContentValues();
		values.put(MSQLiteOpenHelper.TITLE, mediaInfo._title);
		values.put(MSQLiteOpenHelper.ALBUM, mediaInfo._album);
		values.put(MSQLiteOpenHelper.ARTIST, mediaInfo._artist);
		values.put(MSQLiteOpenHelper.DURATION, mediaInfo._duration);
		values.put(MSQLiteOpenHelper.PATH, mediaInfo._path);
		values.put(MSQLiteOpenHelper.PLAYLIST, mediaInfo._playlist);
		return values;
	}

	private List<MediaInfo> getMediaInfo(Cursor cursor) {
		if (cursor == null || cursor.getCount() == 0) {
			return null;
		}
		List<MediaInfo> medias = new ArrayList<MediaInfo>();
		cursor.moveToFirst();
		for (int i = 0; i < cursor.getCount(); i++) {

			MediaInfo mediaInfo = new MediaInfo(cursor.getString(0), cursor
					.getString(3), cursor.getString(1), cursor.getString(6),
					cursor.getString(4), cursor.getString(2), cursor
							.getString(5));
			medias.add(mediaInfo);
			cursor.moveToNext();
		}
		cursor.close();
		return medias;
	}

}
